package org.ielts.repository;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.swing.plaf.basic.BasicSplitPaneUI.PropertyHandler;

import org.apache.commons.lang.ArrayUtils;
import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Property;
import org.hibernate.criterion.Restrictions;
import org.hibernate.criterion.Subqueries;
import org.hibernate.type.StandardBasicTypes;
import org.hibernate.type.Type;
import org.ielts.domain.base.Classroom;
import org.ielts.domain.base.Lesson;
import org.ielts.domain.base.Student;
import org.ielts.domain.log.OrderLessonLog;
import org.ielts.domain.report.LessonQuery;
import org.ielts.domain.report.LessonWithListenerCount;
import org.ielts.domain.report.TeacherBlankLessonReport;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.repository.CrudRepository;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import org.springframework.stereotype.Repository;

import com.yifeng.dao.HibernateRepository;
import com.yifeng.page.table.Paginations;

@Repository
public class LessonRepository extends HibernateRepository<Lesson, String> {

	public Iterable<Lesson> findBetweenTime(Date beginTime, Date endTime) {
		DetachedCriteria criteria = createDetachedCriteria();

		criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
				.add(Restrictions.between("beginTime", beginTime, endTime)).addOrder(Order.asc("beginTime"))
				.addOrder(Order.asc("endTime"));

		@SuppressWarnings("unchecked")
		Iterable<Lesson> lessons = hibernateTemplate.findByCriteria(criteria);

		return lessons;
	}

	public Iterable<Lesson> findBetweenTimeAndClassroom(Date beginTime, Date endTime, String classroomId) {
		DetachedCriteria criteria = createDetachedCriteria();
		criteria.add(Restrictions.between("beginTime", beginTime, endTime)).createCriteria("classroom")
				.add(Restrictions.eq("id", classroomId));

		@SuppressWarnings("unchecked")
		Iterable<Lesson> lessons = hibernateTemplate.findByCriteria(criteria);

		return lessons;
	}

	public Iterable<Lesson> findBetweenDateAndCourseType(Date beginTime, Date endTime, String courseTypeId) {
		DetachedCriteria criteria = createDetachedCriteria();

		criteria.add(Restrictions.between("beginTime", beginTime, endTime)).createCriteria("courseType")
				.add(Restrictions.eq("id", courseTypeId));

		@SuppressWarnings("unchecked")
		Iterable<Lesson> lessons = hibernateTemplate.findByCriteria(criteria);

		return lessons;
	}

	public Page<Lesson> findLesson(LessonQuery lessonQuery, Pageable pageable) {
		DetachedCriteria criteria = createDetachedCriteria();

		criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

		if (lessonQuery.hasCanceleds()) {
			criteria.add(Restrictions.in("canceled", ArrayUtils.toObject(lessonQuery.getCanceleds())));
		}

		if (lessonQuery.hasDate()) {
			criteria.add(Restrictions.between("beginTime", lessonQuery.getDate().getStartDate(), lessonQuery.getDate()
					.getEndDate()));
		}

		if (lessonQuery.hasTeacherIds()) {
			criteria.createCriteria("teachings").createCriteria("teacher")
					.add(Restrictions.in("id", lessonQuery.getTeacherIds()));
		}

		if (lessonQuery.hasCampus()) {
			criteria.createCriteria("classroom").createCriteria("campus")
					.add(Restrictions.eq("id", lessonQuery.getCampus()));
		}

		criteria.addOrder(Order.desc("beginTime"));

		@SuppressWarnings("unchecked")
		List<Lesson> lessons = hibernateTemplate.findByCriteria(criteria, pageable.getOffset(), pageable.getPageSize());

		long total = countRowByCriteria(criteria);

		return new PageImpl<Lesson>(lessons, pageable, total);
	}

	public long countLesson(LessonQuery lessonQuery) {
		DetachedCriteria criteria = createDetachedCriteria();

		if (lessonQuery.hasCanceleds()) {
			criteria.add(Restrictions.in("canceled", ArrayUtils.toObject(lessonQuery.getCanceleds())));
		}

		if (lessonQuery.hasDate()) {
			criteria.add(Restrictions.between("beginTime", lessonQuery.getDate().getStartDate(), lessonQuery.getDate()
					.getEndDate()));
		}

		if (lessonQuery.hasTeacherIds()) {
			criteria.createCriteria("teachings").createCriteria("teacher")
					.add(Restrictions.in("id", lessonQuery.getTeacherIds()));
		}

		long total = countRowByCriteria(criteria);

		return total;
	}

	public Iterable<LessonWithListenerCount> findReportBetweenTimeAndClassroom(Date beginTime, Date endTime,
			String classroomId) {
		String hql = "select new org.ielts.domain.report.LessonWithListenerCount(l, "
				+ "(select count(o.id) from OrderLessonLog o where o.lesson.id = l.id) as numOfListeners )"
				+ "from Lesson l "
				+ "where l.classroom.id = :classroomId and l.beginTime between :beginTime and :endTime order by l.beginTime";

		@SuppressWarnings("unchecked")
		Iterable<LessonWithListenerCount> list = hibernateTemplate.findByNamedParam(hql, new String[] { "classroomId",
				"beginTime", "endTime" }, new Object[] { classroomId, beginTime, endTime });

		return list;
	}

	public Iterable<LessonWithListenerCount> findReportBetweenDateAndCourseType(Date beginTime, Date endTime,
			String courseTypeId) {
		String hql = "select new org.ielts.domain.report.LessonWithListenerCount(l, "
				+ "(select count(o.id) from OrderLessonLog o where o.lesson.id = l.id) as numOfListeners )"
				+ "from Lesson l "
				+ "where l.courseType.id = :courseTypeId and l.beginTime between :beginTime and :endTime order by l.beginTime";

		@SuppressWarnings("unchecked")
		Iterable<LessonWithListenerCount> list = hibernateTemplate.findByNamedParam(hql, new String[] { "courseTypeId",
				"beginTime", "endTime" }, new Object[] { courseTypeId, beginTime, endTime });

		return list;
	}

	public Iterable<LessonWithListenerCount> findReportBetweenDate(Date beginTime, Date endTime) {
		String hql = "select new org.ielts.domain.report.LessonWithListenerCount(l, "
				+ "(select count(o.id) from OrderLessonLog o where o.lesson.id = l.id) as numOfListeners )"
				+ "from Lesson l " + "where l.beginTime between :beginTime and :endTime order by l.beginTime";

		@SuppressWarnings("unchecked")
		Iterable<LessonWithListenerCount> list = hibernateTemplate.findByNamedParam(hql, new String[] { "beginTime",
				"endTime" }, new Object[] { beginTime, endTime });

		return list;
	}

	public long getBlankLessionNumByTeacher(String teacherId) {
		String hql = "select  sum(case count(o.id) when 0 then 1 else 0 end) "
				+ "from Teaching t left join t.lesson l left join l.orders o left join t.teacher c where c.id=:teacherId group by l.id";

		List list = hibernateTemplate.findByNamedParam(hql, "teacherId", teacherId);
		
		long num = firstLongQuery(list);

		return num;

	}

	public List<TeacherBlankLessonReport> findTeacherLessonCount(String roleNameLike) {
		String hql = "select new org.ielts.domain.report.TeacherBlankLessonReport(u.id, u.name,count(t.id) )"
				+ "from User u left join u.roles r left join u.teachings t " + "where r.name like :name_like "
				+ "group by u.name,u.id " + "order by u.name asc ";

		@SuppressWarnings("unchecked")
		List<TeacherBlankLessonReport> list = hibernateTemplate.findByNamedParam(hql, "name_like", roleNameLike);

		return list;
	}

	public Iterable<Lesson> findBetweenTime(Date beginTime, Date endTime, String campusId) {
		DetachedCriteria criteria = createDetachedCriteria();

		criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
				.add(Restrictions.between("beginTime", beginTime, endTime)).addOrder(Order.asc("beginTime"))
				.addOrder(Order.asc("endTime")).createCriteria("classroom").createCriteria("campus")
				.add(Restrictions.eq("id", campusId));

		@SuppressWarnings("unchecked")
		List<Lesson> lessons = hibernateTemplate.findByCriteria(criteria);

		return lessons;
	}

	public Iterable<LessonWithListenerCount> findReportBetweenDateAndCourseTypeAndCampus(Date beginTime, Date endTime,
			String courseTypeId, String campusId) {

		String hql = "select new org.ielts.domain.report.LessonWithListenerCount(l, "
				+ "(select count(o.id) from OrderLessonLog o where o.lesson.id = l.id) as numOfListeners )"
				+ "from Lesson l "
				+ "where l.courseType.id = :courseTypeId and l.beginTime between :beginTime and :endTime and l.classroom.campus.id = :campus order by l.beginTime";

		@SuppressWarnings("unchecked")
		List<LessonWithListenerCount> list = hibernateTemplate.findByNamedParam(hql, new String[] { "courseTypeId",
				"beginTime", "endTime", "campus" }, new Object[] { courseTypeId, beginTime, endTime, campusId });

		return list;
	}

	public Iterable<Lesson> findBetweenTime(Date beginTime, Date endTime, Sort sort) {
		DetachedCriteria criteria = createDetachedCriteria();

		criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).add(
				Restrictions.between("beginTime", beginTime, endTime));

		buildBySort(criteria, sort);

		@SuppressWarnings("unchecked")
		Iterable<Lesson> lessons = hibernateTemplate.findByCriteria(criteria);

		return lessons;
	}

	public Iterable<Lesson> findBetweenTime(Date beginTime, Date endTime, String campusId, Sort sort) {
		DetachedCriteria criteria = createDetachedCriteria();

		criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).add(
				Restrictions.between("beginTime", beginTime, endTime));

		buildBySort(criteria, sort);

		criteria.createCriteria("classroom").createCriteria("campus").add(Restrictions.eq("id", campusId));

		@SuppressWarnings("unchecked")
		Iterable<Lesson> lessons = hibernateTemplate.findByCriteria(criteria);

		return lessons;
	}
}
